using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;


namespace PipelineTemplateEditor.Util
{
	public class ComboBoxItem
	{
		private string _value;

		public string Value
		{
			get { return _value; }
			set { _value = value; }
		}
		private string _label;

		public string Label
		{
			get { return _label; }
			set { _label = value; }
		}

		public ComboBoxItem(string v, string l)
		{
			_value = v;
			_label = l;
		}

		public override string ToString()
		{
			return Label;
		}
	}

    public class ConnectionManager
    {
        public static string ConnectionString = ConfigurationManager.AppSettings["ServerConnection"];

        public static List<ComboBoxItem> GetFieldsList() 
        {
            return GetList("SELECT fields_name, fields_value FROM fields ORDER BY fields_name");
        }

        public static List<ComboBoxItem> GetTemplateList()
        {
            return GetList("SELECT row_template_name, CAST (row_template_id AS varchar(5)) As [row_template_id] FROM row_template ORDER BY row_template_name");
        }

        public static List<ComboBoxItem> GetList(string sql)
        {
            SqlDataReader dr = null;
            List<ComboBoxItem> items = new List<ComboBoxItem>();
            try
            {
                SqlConnection cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                    items.Add(new ComboBoxItem(dr.GetString(1), dr.GetString(0)));

                dr.Close();
            }
            catch (Exception e)
            {
                // do nothing
            }
            finally
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
            }
            return items;
        }
    }
}
